﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.Common;
using System.Data.SqlClient;
using Jacaranda.Model;

namespace Jacaranda.Data
{
    class Sql2000DataAccess : DataAccess
    {
        public override DbConnection CreateDbConnection()
        {
            return new SqlConnection();
        }

        public override DbCommand CreateDbCommand()
        {
            return new SqlCommand();
        }

        public override DbDataAdapter CreateDbDataAdapter()
        {
            return new SqlDataAdapter();
        }

        public override DbCommandBuilder CreateDbCommandBuilder()
        {
            return new SqlCommandBuilder();
        }

        public override DbParameter CreateDbParameter()
        {
            return new SqlParameter();
        }

        public override string AssociationTableTimestampColumnName
        {
            get { return "Timestamp"; }
        }

        public override string CreateParameterizedExpression(string parameterName)
        {
            return string.Format("@{0}", parameterName);
        }

        public override void DeleteTableData(string tableName)
        {
            DataPortal.Execute(string.Format("IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME='{0}') DELETE FROM {1}", tableName, tableName));
        }

        public override void DropTable(string tableName)
        {
            DataPortal.Execute(string.Format("IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME='{0}') DROP TABLE {1}", tableName, tableName));
        }

        public override void DropFunction(string functionName)
        {
            DataPortal.Execute(string.Format("IF EXISTS (SELECT * FROM dbo.sysobjects WHERE object_id = OBJECT_ID(N'[dbo].[{0}]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT')) DROP FUNCTION [dbo].[{1}]", functionName, functionName));
        }

        public override string CreatePagingSelectStatement(ConditionalExpression expression, Type entityType, string fields, SortCriteria sort, SearchPaginationCriteria page)
        {
            EntityMetadata m = Metadata.Type(entityType);

            if (page.SelectStart == 1)
            {
                return string.Format("SELECT TOP {0} {1} FROM {2} {3} {4}",
                page.PageLength,
                fields,
                m.TableName,
                (expression is SelectAllCriteria) ? null : ("WHERE " + expression.GenerateSql(entityType)),
                (sort == null) ? null : sort.GenerateSql());
            }
            else
            {
                return string.Format("SELECT TOP {0} {1} FROM {2} WHERE {3} {4} NOT IN (SELECT TOP {5} {6} FROM {7} {8} {9}) {10}",
                      page.PageLength,
                      fields,
                      m.TableName,
                      (expression is SelectAllCriteria) ? null : (expression.GenerateSql(entityType) + " AND"),
                      m.DbPrimaryKey.FieldName,
                      page.SelectStart - 1,
                      m.DbPrimaryKey.FieldName,
                      m.TableName,
                      (expression is SelectAllCriteria) ? null : ("WHERE " + expression.GenerateSql(entityType)),
                      (sort == null) ? null : sort.GenerateSql(),
                      (sort == null) ? null : sort.GenerateSql());
            }
        }
    }
}
